<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Reportes_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    function repa($suc, $fi, $ff, $ta_tipo_aspi_id) {
        $sucursales = "";
        foreach ($suc as $v) {
            $sucursales.=$v . ",";
        }
        $sucursales = trim($sucursales, ",");

        $query = "  
            select      'A' as categoria,    
                        round((( b.puntos_posibles - b.pdesviaciones)/b.puntos_posibles)*100) as calific     
                        from (           
                            select  sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as pdesviaciones,  
                                            sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos, 
                                            sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones, 
                                            sum(p.p_pp) as puntos_posibles  
                            FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                            join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                            join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                            join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                            join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                            join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                    join sucursal s on(s.s_sucursal_id=cu.s_sucursal_id)

                            where opn.on_opcion!='NO APLICA'  
                            and ta.ta_tipo_aspi_id= $ta_tipo_aspi_id    
                            and s.s_sucursal_id in($sucursales)   /* sucursales <list> */      
                            and c.c_categoria='A' 
                            and cu.c_fecha between '$fi' and '$ff'
                    ) as b  
            union
            select      'S' as categoria,    
                        round((( b.puntos_posibles - b.pdesviaciones)/b.puntos_posibles)*100) as calific     
                    from (           
                            select  sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as pdesviaciones,  
                                            sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos, 
                                            sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones, 
                                            sum(p.p_pp) as puntos_posibles  
                            FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                            join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                            join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                            join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                            join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                            join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                    join sucursal s on(s.s_sucursal_id=cu.s_sucursal_id)

                            where opn.on_opcion!='NO APLICA'  
                            and ta.ta_tipo_aspi_id= $ta_tipo_aspi_id    
                            and s.s_sucursal_id in($sucursales)   /* sucursales <list> */      
                            and c.c_categoria='S' 
                            and cu.c_fecha between '$fi' and '$ff'
                    ) as b  
            union
            select      'P' as categoria,    
                        round((( b.puntos_posibles - b.pdesviaciones)/b.puntos_posibles)*100) as calific     
                        from (           
                            select  sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as pdesviaciones,  
                                            sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos, 
                                            sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones, 
                                            sum(p.p_pp) as puntos_posibles  
                            FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                            join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                            join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                            join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                            join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                            join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                            join sucursal s on(s.s_sucursal_id=cu.s_sucursal_id)

                            where opn.on_opcion!='NO APLICA'  
                            and ta.ta_tipo_aspi_id= $ta_tipo_aspi_id    
                            and s.s_sucursal_id in($sucursales)   /* sucursales <list> */      
                            and c.c_categoria='P' 
                            and cu.c_fecha between '$fi' and '$ff'
                    ) as b 
             union
             select     'I' as categoria,    
                        round((( b.puntos_posibles - b.pdesviaciones)/b.puntos_posibles)*100) as calific     
                        from (           
                            select  sum(case opn.on_opcion when 'DESVIACION' then p.p_pp else 0 end) as pdesviaciones,  
                                            sum(case opn.on_opcion when 'CRITICO' then 1 else 0 end) as criticos, 
                                            sum(case opn.on_opcion when 'DESVIACION' then 1 else 0 end) as desviaciones, 
                                            sum(p.p_pp) as puntos_posibles  
                            FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                            join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                            join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                            join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                            join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                            join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                            join sucursal s on(s.s_sucursal_id=cu.s_sucursal_id)

                            where opn.on_opcion!='NO APLICA'  
                            and ta.ta_tipo_aspi_id= $ta_tipo_aspi_id    
                            and s.s_sucursal_id in($sucursales)   /* sucursales <list> */      
                            and c.c_categoria='I' 
                            and cu.c_fecha between '$fi' and '$ff'
                    ) as b  ";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    public function repb($ta_tipo_aspi_id, $r_region_id) {
        $query = "select  b.s_descripcion as unidad,
                                b.r_no as zona,
                                round((( b.puntos_posibles - b.pdesviaciones1)/b.puntos_posibles)*100) as erc1,
                                round((( b.puntos_posibles - b.pdesviaciones2)/b.puntos_posibles)*100) as erc2,
                                round((( b.puntos_posibles - b.pdesviaciones3)/b.puntos_posibles)*100) as erc3,
                                round( (round((( b.puntos_posibles - b.pdesviaciones1)/b.puntos_posibles)*100)+round((( b.puntos_posibles - b.pdesviaciones2)/b.puntos_posibles)*100)+round((( b.puntos_posibles - b.pdesviaciones3)/b.puntos_posibles)*100))/3 ) as total 
                from (           
                                select  sum(if ((opn.on_opcion = 'DESVIACION' and cu.c_fecha between concat(YEAR(CURDATE()) ,'-01-01') and concat(YEAR(CURDATE()),'-04-30' )), p.p_pp , 0 )) as pdesviaciones1,
                                                sum(if ((opn.on_opcion = 'DESVIACION' and cu.c_fecha between concat(YEAR(CURDATE()) ,'-05-01') and concat(YEAR(CURDATE()),'-06-31' )), p.p_pp , 0 )) as pdesviaciones2,
                                                sum(if ((opn.on_opcion = 'DESVIACION' and cu.c_fecha between concat(YEAR(CURDATE()) ,'-07-01') and concat(YEAR(CURDATE()),'-12-31' )), p.p_pp , 0 )) as pdesviaciones3,  
                                                sum(p.p_pp) as puntos_posibles,
                                                s.s_descripcion,
                                r.r_no

                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                                join sucursal s on(s.s_sucursal_id=cu.s_sucursal_id)
                                join region r on(r.r_region_id=s.r_region_id)

                                where opn.on_opcion!='NO APLICA'  
                                and ta.ta_tipo_aspi_id= ? /* tipo de aspi */
                                and r.r_region_id = ? /* region */

                                group by s.s_descripcion

                        ) as b
        union
        select  'TOTAL' as unidad,
                                '' as zona,
                                round((( b.puntos_posibles - b.pdesviaciones1)/b.puntos_posibles)*100) as erc1,
                                round((( b.puntos_posibles - b.pdesviaciones2)/b.puntos_posibles)*100) as erc2,
                                round((( b.puntos_posibles - b.pdesviaciones3)/b.puntos_posibles)*100) as erc3,
                                round( (round((( b.puntos_posibles - b.pdesviaciones1)/b.puntos_posibles)*100)+round((( b.puntos_posibles - b.pdesviaciones2)/b.puntos_posibles)*100)+round((( b.puntos_posibles - b.pdesviaciones3)/b.puntos_posibles)*100))/3 ) as total 
                from (           
                                select  sum(if ((opn.on_opcion = 'DESVIACION' and cu.c_fecha between concat(YEAR(CURDATE()) ,'-01-01') and concat(YEAR(CURDATE()),'-04-30' )), p.p_pp , 0 )) as pdesviaciones1,
                                                sum(if ((opn.on_opcion = 'DESVIACION' and cu.c_fecha between concat(YEAR(CURDATE()) ,'-05-01') and concat(YEAR(CURDATE()),'-06-31' )), p.p_pp , 0 )) as pdesviaciones2,
                                                sum(if ((opn.on_opcion = 'DESVIACION' and cu.c_fecha between concat(YEAR(CURDATE()) ,'-07-01') and concat(YEAR(CURDATE()),'-12-31' )), p.p_pp , 0 )) as pdesviaciones3,  
                                                sum(p.p_pp) as puntos_posibles,
                                                s.s_descripcion,
                                r.r_no

                                FROM pregunta p join subcategoria sc  on(p.s_subcategoria_id=sc.s_subcategoria_id)     
                                join categoria c on(c.c_categoria_id=sc.c_categoria_id)     
                                join tipo_aspi ta on(c.ta_tipo_aspi_id=ta.ta_tipo_aspi_id)   
                                join calificacion cal on(cal.p_pregunta_id=p.p_pregunta_id)   
                                join cuestionario cu on(cu.c_cuestionario_id=cal.c_cuestionario_id)   
                                join opcion_na opn on(opn.on_opcion_na_id=cal.on_opcion_na_id)
                                join sucursal s on(s.s_sucursal_id=cu.s_sucursal_id)
                                join region r on(r.r_region_id=s.r_region_id)

                                where opn.on_opcion!='NO APLICA'  
                                and ta.ta_tipo_aspi_id= ? /* tipo de aspi */
                                and r.r_region_id = ? /* region */
                        ) as b        ";
        $resultado = $this->db->query($query, array($ta_tipo_aspi_id, $r_region_id, $ta_tipo_aspi_id, $r_region_id));
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    public function lista_regiones() {
        $query = "SELECT r_region_id,r_no FROM region";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    public function iBull($finicial, $ffinal, $ta_tipo_aspi_id) {
        $iQuery = "";       #variable que almacenará el query final
        $condicion = "";    #variable que almacena la condicion
        $cuestionariosXsucursal = "  SELECT     distinct s.s_sucursal_id,
                                                c.c_cuestionario_id,
                                                s.s_descripcion as sucursal
                                    FROM cuestionario c  
                                    join sucursal s on (c.s_sucursal_id=s.s_sucursal_id)
                                    join calificacion cal on(cal.c_cuestionario_id=c.c_cuestionario_id)
                                    join pregunta p on(p.p_pregunta_id=cal.p_pregunta_id)
                                    join subcategoria sub on(sub.s_subcategoria_id=p.s_subcategoria_id)
                                    join categoria cat on(cat.c_categoria_id=sub.c_categoria_id)
                                    join tipo_aspi ta on(ta.ta_tipo_aspi_id=cat.ta_tipo_aspi_id) 
                                    where   c.c_fecha between ? and ?
                                            and ta.ta_tipo_aspi_id = ?
                                    group by s.s_sucursal_id
                                    ORDER BY    s.s_sucursal_id,
                                                s.s_descripcion,
                                                c.c_cuestionario_id ASC";

        $resultado = $this->db->query($cuestionariosXsucursal, array($finicial, $ffinal, $ta_tipo_aspi_id));
        $cuestonarios = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();

        $pA = "   SELECT    concat(cat.c_categoria,'-',p.p_matricula) as matricula, 
		            p.p_pregunta, 
		            p.p_pp,";
        $pC = "     FROM sucursal s 
			join cuestionario c on(s.s_sucursal_id=c.s_sucursal_id) 
			join calificacion ca on(ca.c_cuestionario_id=c.c_cuestionario_id) 
			join opcion_na onn on(onn.on_opcion_na_id=ca.on_opcion_na_id) 
			join pregunta p on(p.p_pregunta_id=ca.p_pregunta_id) 
			join subcategoria sc on(sc.s_subcategoria_id=p.s_subcategoria_id) 
			join categoria cat on(cat.c_categoria_id=sc.c_categoria_id) 
			join tipo_aspi ta on(ta.ta_tipo_aspi_id=cat.ta_tipo_aspi_id)
                        where ta.ta_tipo_aspi_id = $ta_tipo_aspi_id
			group by 	
					concat(cat.c_categoria,'-',p.p_matricula), 
					p.p_pregunta, 
					p.p_pp
			order by    ca.c_cuestionario_id asc,
                                    cat.c_categoria_id asc,
                                    p.p_matricula asc";
        # comienza construcción de la consulta
        foreach ($cuestonarios as $c) {
            $condicion.=" sum(case when (        onn.on_opcion_na_id =1 
                                            and s.s_sucursal_id = $c->s_sucursal_id 
                                            and c.c_cuestionario_id in ";
            #lista de cuestionarios
            $plist2 = " ( "; #(1,2,3..)
            $lcuestionarios = "  SELECT     distinct s.s_sucursal_id,
                                                c.c_cuestionario_id,
                                                s.s_descripcion as sucursal
                                    FROM cuestionario c  
                                    join sucursal s on (c.s_sucursal_id=s.s_sucursal_id)
                                    join calificacion cal on(cal.c_cuestionario_id=c.c_cuestionario_id)
                                    join pregunta p on(p.p_pregunta_id=cal.p_pregunta_id)
                                    join subcategoria sub on(sub.s_subcategoria_id=p.s_subcategoria_id)
                                    join categoria cat on(cat.c_categoria_id=sub.c_categoria_id)
                                    join tipo_aspi ta on(ta.ta_tipo_aspi_id=cat.ta_tipo_aspi_id) 
                                    where   c.c_fecha between ? and ?
                                            and ta.ta_tipo_aspi_id = ?
                                            and s.s_sucursal_id= ?
                                    ORDER BY    s.s_sucursal_id,
                                                s.s_descripcion,
                                                c.c_cuestionario_id ASC";
            $l = $this->db->query($lcuestionarios, array($finicial, $ffinal, $ta_tipo_aspi_id, $c->s_sucursal_id));
            $lc = $l->result();
            $l->next_result();
            $l->free_result();
            foreach ($lc as $l) {
                $plist2 = $plist2 . "$l->c_cuestionario_id,";
            }
            $plist2 = trim($plist2, ',') . ")"; # elimina la ',' extra y cierra la lista ...)
            $condicion = $condicion . $plist2 . " and p.p_pregunta_id in ";
            # lista de preguntas
            $plist = " ( "; #(1,2,3..)
            $iBuilder = "select 	
                                s.s_sucursal_id,
                                c.c_cuestionario_id,
                                p.p_pregunta_id,
                                onn.on_opcion_na_id,
                                s.s_descripcion as sucursal,
                                p.p_pregunta
                        from sucursal s 
                        join cuestionario c on(s.s_sucursal_id=c.s_sucursal_id) join calificacion ca on(ca.c_cuestionario_id=ca.c_cuestionario_id)
                        join opcion_na onn on(onn.on_opcion_na_id=ca.on_opcion_na_id) join pregunta p on(p.p_pregunta_id=ca.p_pregunta_id)

                        where 	s.s_sucursal_id=?

                        group by    s.s_sucursal_id, 
                                    p_pregunta_id
                        order by    s.s_sucursal_id,
                                    c_cuestionario_id,
                                    p_pregunta_id";

            $xRes = $this->db->query($iBuilder, array($c->s_sucursal_id));
            $iList = $xRes->result();
            $xRes->next_result();
            $xRes->free_result();
            # este ciclo genera la lista de preguntas dentro del la lista
            foreach ($iList as $i) {
                $plist = $plist . "$i->p_pregunta_id,";
            }
            $plist = trim($plist, ',') . ")"; # elimina la ',' extra y cierra la lista ...) 
            $plist = $plist . " ) then 1  else 0 end ) '".str_replace(' ','_',strtolower($c->sucursal))."' ,";
            $condicion = $condicion . $plist;
        }
        $condicion = trim($condicion, ',');
        $iQuery = $pA . $condicion . $pC;

        $r = $this->db->query($iQuery);
        $bQuery = $r->result_array();
        $r->next_result();
        $r->free_result();

        return $bQuery;
    }

    public function xUnidades($finicial, $ffinal, $ta_tipo_aspi_id) {
        $q = "  SELECT  distinct s.s_sucursal_id,
                            c.c_cuestionario_id,
                            s.s_descripcion as sucursal
                    FROM cuestionario c  
                    join sucursal s on (c.s_sucursal_id=s.s_sucursal_id)
                    join calificacion cal on(cal.c_cuestionario_id=c.c_cuestionario_id)
                    join pregunta p on(p.p_pregunta_id=cal.p_pregunta_id)
                    join subcategoria sub on(sub.s_subcategoria_id=p.s_subcategoria_id)
                    join categoria cat on(cat.c_categoria_id=sub.c_categoria_id)
                    join tipo_aspi ta on(ta.ta_tipo_aspi_id=cat.ta_tipo_aspi_id) 
                    where   c.c_fecha between ? and ?
                            and ta.ta_tipo_aspi_id = ?
                    group by s.s_sucursal_id        
                    ORDER BY    s.s_sucursal_id, s.s_descripcion,
                                c.c_cuestionario_id ASC";
        $resultado = $this->db->query($q, array($finicial, $ffinal, $ta_tipo_aspi_id));
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }
    
    public function getListSupervisores(){
        $query = "SELECT concat(a.a_nombre,' ',a.a_apellido) a_nombre,
                                a.a_administrativo_id 
                FROM administrativo a 
                where a.ta_tipo_administrativo_id = 1";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }
    
    public function getListSucursalesXSuper($a_administrativo_id){
        $query = "SELECT s_sucursal_id,
                    s_descripcion
            FROM sucursal 
            where a_admin_serv_id = $a_administrativo_id or a_admin_cocina_id = $a_administrativo_id";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

}
